﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using VSSAHT.DataAccess;
using VSSAHT.Transfer;
using System.Data.OleDb;
using VSS_AHT.Properties;
namespace VSS_AHT
{
    public partial class FormItems : BaseForm
    {

        #region Singleton Members
        private static volatile FormItems instance;
        private static object syncRoot = new Object();

        public static FormItems Instance
        {
            get
            {
                lock (syncRoot)
                {
                    if (instance == null)
                        instance = new FormItems();
                }

                return instance;
            }
        }
        #endregion

        #region Field Members
        ItemDataAccess _dataAccess = new ItemDataAccess();
        
        IList<ItemDTO> _itemList;
        #endregion

        #region Ctors
        public FormItems()
        {
            InitializeComponent();
            Initializes();
        }

        public void Initializes()
        {
            this.rdbItemCode.CheckedChanged += new EventHandler(delegate { ClickRdbItemCode(); });
            this.rdbItemName.CheckedChanged += new EventHandler(delegate { ClickRdbItemCode(); });
            this.btnSearch.Click += new EventHandler(delegate { ClickButtonSearch(); });
            this.btnReset.Click += new EventHandler(delegate { ClickButtonReset(); });
   
            this.btnNew.Click += new EventHandler(delegate { ClickButtonNew(); });
            this.btnEdit.Click += new EventHandler(delegate { ClickButtonEdit(); });
            this.btnRemove.Click += new EventHandler(delegate { ClickButtonDelete(); });
            this.btnLoadExcel.Click += new EventHandler(btnLoadExcel_Click);
            this.btnClose.Click += new EventHandler(delegate { this.Close(); });
            this.Disposed += new EventHandler(delegate { instance = null; });
            this.ClickButtonSearch();
            
        }
        #endregion

        #region Methods
        private void ClickRdbItemCode()
        {
            if (rdbItemCode.Checked)
            {
                this.txtItemCode.Enabled = true;
                this.txtItemName.Enabled = false;
                this.txtItemCode.Focus();
            }
            else
            {
                this.txtItemCode.Enabled = false;
                this.txtItemName.Enabled = true;
                this.txtItemName.Focus();
            }
        }
        private void ClickButtonSearch()
        {
            
            _dataAccess.SearchObject = new ItemDTO();
            _dataAccess.SearchObject.ItemNumber = this.txtItemCode.Text;
            _dataAccess.SearchObject.ItemName = this.txtItemName.Text;

            _itemList = _dataAccess.Search();

            this.gridControl1.DataSource = _itemList; ;
           
        }

        private void ClickButtonReset()
        {
            this.txtItemCode.Text = string.Empty;
            this.txtItemName.Text = string.Empty;
            this.ClickButtonSearch();
        }

        private void ClickButtonNew()
        {
            int count = _itemList.Count;
            // Show the updating staff instance
            FormUpdateItem instance = new FormUpdateItem();
            instance.Collection = _itemList;
            instance.ShowDialog();

            if (count < _itemList.Count)
            {
                // Refresh data
                this.ClickButtonSearch();
            }
           
        }

        private void ClickButtonEdit()
        {
            if (this.gridView1.GetFocusedRow() == null)
            {
                Common.ShowAlert("Vui lòng cho dòng cần chỉnh sửa");
                return;
            }

            // Edit  information
            FormUpdateItem instance = new FormUpdateItem();
            instance.Item = this.gridView1.GetFocusedRow() as ItemDTO;
            DialogResult result = instance.ShowDialog();
            if (result == DialogResult.OK)
            {
                // Refresh data
                this.gridView1.RefreshData();
            }
           
        }

        private void ClickButtonDelete()
        {
            try
            {
                if (this.gridView1.GetFocusedRow() == null )
                {
                    Common.ShowAlert("Vui lòng chọn dòng cần xóa!");
                    return;
                }

                ItemDTO item = this.gridView1.GetFocusedRow() as ItemDTO;
                bool? result = Common.ShowYesNoQuestion("Bạn có chắc chắn muốn xóa thông tin này?");
                if (result.HasValue && result.Value)
                {
                    string itemNumber = item.ItemNumber ;
                    bool r = _dataAccess.Delete(item);

                    if (r)
                    {
                        this.gridView1.DeleteSelectedRows();
                        this.gridView1.RefreshData();
                    }
                    else
                    {
                        Common.ShowAlert("Quá trình xóa vật tư không thành công.");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLoadExcel_Click(object sender, EventArgs e)
        {
            //get file path and name
            string filename = string.Empty;
            DataTable dt = new DataTable();
            OpenFileDialog ofd = new OpenFileDialog();
            filename = ofd.ShowDialog() == DialogResult.OK ? ofd.FileName : "";

            //read data from excel to datatable
            if (filename.Length > 0)
            {
                dt = ReadDatafromExcel(filename);
                //Save data to SQL server
                ImportDataToSQL(dt);
                //Load Data to Grid
                this.ClickButtonSearch();
                this.gridView1.RefreshData();
            }
        }
        #endregion

        #region ImportExportDataFromExcelToSQLServer
        private DataTable ReadDatafromExcel(string filename)
        {
            //Excel 2003 or Excel 2007
            string[] filetags = filename.Split('.');
            string connectionString = string.Empty;
            if (filename.Length > 1 && filetags[1] == "xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";
            }
            else
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0";
            }

            // initial connection

            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            DataTable data = null;
            try
            {
                oledbConn.Open();
                //initial OleDbCommand object and query data from sheet1
                OleDbCommand cmd = new OleDbCommand("select * from[Sheet1$]", oledbConn);
                OleDbDataAdapter oledata = new OleDbDataAdapter();
                oledata.SelectCommand = cmd;

                // initial DataSet object to get data from Excel
                DataSet ds = new DataSet();
                oledata.Fill(ds);
                data = ds.Tables[0];

            }
            catch (Exception ex)
            {
                Common.ShowAlert(Resources.ReadDataFromExcel + ex.Message);
            }
            finally
            {
                oledbConn.Close();
            }
            return data;

        }

        private void ImportDataToSQL(DataTable data)
        {
            if (data == null || data.Rows.Count == 0)
            {
                Common.ShowAlert(Resources.NotDataToImport);
            }
            try
            {
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    ItemDTO _item = new ItemDTO();
                    _item.ItemNumber = data.Rows[i][0].ToString().Trim();
                    _item.ItemName = data.Rows[i][1].ToString().Trim();
                    _item.ItemUnit = data.Rows[i][2].ToString().Trim();
                    _item.ItemUnit2 = data.Rows[i][3].ToString().Trim();
                    //Test exist code
                    _dataAccess.SearchObject = new ItemDTO();
                    _dataAccess.SearchObject.ItemNumber = _item.ItemNumber;
                    _dataAccess.SearchObject.ItemName = _item.ItemName;

                    _itemList = _dataAccess.Search();

                    if (_itemList.Count == 0)
                    {
                        _dataAccess.Add(_item);
                    }
                }
            }
            catch (Exception ex)
            {
                Common.ShowAlert("Lỗi lưu dữ liệu xuống Database! Lỗi : " + ex.Message);
            }
        }

        #endregion

        #region Events

        #endregion
    }
}